﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Adams.Web.Data
{
    public static class Layout
    {
        public static Adams.Web.Models.Layout.PageLoad PageLoad(int memberId, string url, bool isMobile)
        {
            var data = new Adams.Web.Models.Layout.PageLoad();
            var topnodes = new Shared.Entities.Sitemap.WebTopNodeCollection();
            var bottomnodes = new Shared.Entities.Sitemap.WebBottomNodeCollection();
            data.BreadCrumb = new Shared.Entities.Sitemap.NodeCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
IF(@MemberID > 0)
BEGIN
    SELECT MemberID, FirstName, LastName, Email 
    FROM Membership.Members
    WHERE MemberID = @MemberID
END;

IF(@IsMobile = 1)
BEGIN
    -- Mobile Nodes
    DECLARE @MobileNodeID int
    DECLARE @MobileParentNodeID int
    DECLARE @MobileParentParentNodeID int
    SELECT @MobileNodeID = NodeID, @MobileParentNodeID = ParentNodeID FROM Sitemap.MobileNodes n WHERE n.Url = @Url

    IF(@MobileNodeID IS NULL)
    BEGIN
        SELECT TOP 1 @MobileNodeID = NodeID, @MobileParentNodeID = ParentNodeID FROM Sitemap.MobileNodes n WHERE n.Url <> '/' AND n.Url <> '/myadams' AND CHARINDEX(n.Url, @Url) > 0
    END

    SELECT @MobileParentParentNodeID = ParentNodeID FROM Sitemap.MobileNodes n WHERE n.NodeID = @MobileParentNodeID

    SELECT n.* FROM Sitemap.MobileNodes n WHERE n.NodeID = @MobileNodeID
    SELECT n.* FROM Sitemap.MobileNodes n WHERE n.NodeID = @MobileParentNodeID
    SELECT n.* FROM Sitemap.MobileNodes n WHERE n.ParentNodeID = @MobileParentNodeID ORDER BY DisplayOrder;
    SELECT n.* FROM Sitemap.MobileNodes n WHERE n.ParentNodeID = @MobileNodeID ORDER BY DisplayOrder;
    SELECT n.* FROM Sitemap.MobileNodes n WHERE n.ParentNodeID = @MobileParentParentNodeID ORDER BY DisplayOrder;
END
ELSE
BEGIN
    -- Top Nodes
    WITH AllNodes AS (
	    SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, 0 AS [Level], n.Title, n.Url
	    FROM Sitemap.WebTopNodes n
	    WHERE NodeID = 1
	    UNION ALL
	    SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, a.[Level] + 1, n.Title, n.Url
	    FROM Sitemap.WebTopNodes n JOIN AllNodes a ON n.ParentNodeID = a.NodeID 
    )
    SELECT * 
    FROM AllNodes
    ORDER BY Level, DisplayOrder;

    -- Bottom Nodes
    WITH AllNodes AS (
	    SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, 0 AS [Level], n.Title, n.Url
	    FROM Sitemap.WebBottomNodes n
	    WHERE NodeID = 1
	    UNION ALL
	    SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, a.[Level] + 1, n.Title, n.Url
	    FROM Sitemap.WebBottomNodes n JOIN AllNodes a ON n.ParentNodeID = a.NodeID 
    )
    SELECT * 
    FROM AllNodes
    ORDER BY Level, DisplayOrder;

    -- Left Nodes
    DECLARE @NodeID int
    DECLARE @ParentNodeID int
    DECLARE @ParentParentNodeID int
    SELECT @NodeID = NodeID, @ParentNodeID = ParentNodeID FROM Sitemap.WebLeftNodes n WHERE n.Url = @Url

    IF(@NodeID IS NULL)
    BEGIN
        SELECT TOP 1 @NodeID = NodeID, @ParentNodeID = ParentNodeID FROM Sitemap.WebLeftNodes n WHERE n.Url <> '/' AND n.Url <> '/myadams' AND CHARINDEX(n.Url, @Url) > 0
    END

    SELECT @ParentParentNodeID = ParentNodeID FROM Sitemap.WebLeftNodes n WHERE n.NodeID = @ParentNodeID

    SELECT n.* FROM Sitemap.WebLeftNodes n WHERE n.NodeID = @NodeID
    SELECT n.* FROM Sitemap.WebLeftNodes n WHERE n.NodeID = @ParentNodeID
    SELECT n.* FROM Sitemap.WebLeftNodes n WHERE n.ParentNodeID = @ParentNodeID ORDER BY DisplayOrder;
    SELECT n.* FROM Sitemap.WebLeftNodes n WHERE n.ParentNodeID = @NodeID ORDER BY DisplayOrder;
    SELECT n.* FROM Sitemap.WebLeftNodes n WHERE n.ParentNodeID = @ParentParentNodeID ORDER BY DisplayOrder;

END

-- BreadCrumb Nodes
SELECT @NodeID = NodeID FROM Sitemap.Nodes n WHERE n.Url = @Url

IF(@NodeID IS NULL)
BEGIN
    SELECT TOP 1 @NodeID = NodeID FROM Sitemap.Nodes n WHERE n.Url <> '/' AND n.Url <> '/myadams' AND CHARINDEX(n.Url, @Url) > 0
END;

WITH AllNodes AS (
	SELECT n.NodeID, n.ParentNodeID, 0 AS [Level], n.Title, n.Url
	FROM Sitemap.Nodes n
	WHERE n.NodeID = @NodeID
	UNION ALL
	SELECT n.NodeID, n.ParentNodeID, a.[Level] + 1, n.Title, n.Url
	FROM Sitemap.Nodes n JOIN AllNodes a ON n.NodeID = a.ParentNodeID 
)
SELECT * 
FROM AllNodes
ORDER BY Level DESC;
",
                   CommandType.Text,
                   DB.CreateParameter("@MemberID", memberId),
                   DB.CreateParameter("@Url", url),
                   DB.CreateParameter("@IsMobile", isMobile)))
            {
                if (memberId > 0)
                {
                    dr.Read();
                    data.FirstName = dr["FirstName"].DbString();
                    data.LastName = dr["LastName"].DbString();
                    data.Email = dr["Email"].DbString();
                    dr.NextResult();
                }

                if (isMobile)
                {
                    // Mobile Nav
                    data.CurrentMobileNode = new Shared.Entities.Sitemap.MobileNode()
                    {
                        ChildNodes = new Shared.Entities.Sitemap.MobileNodeCollection(),
                        ParentNode = new Shared.Entities.Sitemap.MobileNode()
                        {
                            ChildNodes = new Shared.Entities.Sitemap.MobileNodeCollection(),
                            ParentNode = new Shared.Entities.Sitemap.MobileNode()
                            {
                                ChildNodes = new Shared.Entities.Sitemap.MobileNodeCollection()
                            }
                        }
                    };

                    if (dr.HasRows)
                    {
                        dr.Read();
                        data.CurrentMobileNode.ID = dr["NodeID"].DbInt();
                        data.CurrentMobileNode.Url = dr["Url"].DbString();
                        data.CurrentMobileNode.Title = dr["Title"].DbString();
                        data.CurrentMobileNode.DisplayOrder = dr["DisplayOrder"].DbInt();
                    }

                    dr.NextResult();
                    if (dr.HasRows)
                    {
                        dr.Read();
                        data.CurrentMobileNode.ParentNode.ID = dr["NodeID"].DbInt();
                        data.CurrentMobileNode.ParentNode.Url = dr["Url"].DbString();
                        data.CurrentMobileNode.ParentNode.Title = dr["Title"].DbString();
                        data.CurrentMobileNode.ParentNode.DisplayOrder = dr["DisplayOrder"].DbInt();
                    }

                    dr.NextResult();
                    while (dr.Read())
                    {
                        var node = new Shared.Entities.Sitemap.MobileNode();
                        node.ID = dr["NodeID"].DbInt();
                        node.Url = dr["Url"].DbString();
                        node.Title = dr["Title"].DbString();
                        node.DisplayOrder = dr["DisplayOrder"].DbInt();
                        data.CurrentMobileNode.ParentNode.ChildNodes.Add(node);
                    }

                    dr.NextResult();
                    while (dr.Read())
                    {
                        var node = new Shared.Entities.Sitemap.MobileNode();
                        node.ID = dr["NodeID"].DbInt();
                        node.Url = dr["Url"].DbString();
                        node.Title = dr["Title"].DbString();
                        node.DisplayOrder = dr["DisplayOrder"].DbInt();
                        data.CurrentMobileNode.ChildNodes.Add(node);
                    }

                    dr.NextResult();
                    while (dr.Read())
                    {
                        var node = new Shared.Entities.Sitemap.MobileNode();
                        node.ID = dr["NodeID"].DbInt();
                        node.Url = dr["Url"].DbString();
                        node.Title = dr["Title"].DbString();
                        node.DisplayOrder = dr["DisplayOrder"].DbInt();
                        data.CurrentMobileNode.ParentNode.ParentNode.ChildNodes.Add(node);
                    }
                }
                else
                {
                    // Top Nav
                    while (dr.Read())
                    {
                        var node = new Shared.Entities.Sitemap.WebTopNode();
                        node.ID = dr["NodeID"].DbInt();
                        node.Url = dr["Url"].DbString();
                        node.Title = dr["Title"].DbString();
                        node.DisplayOrder = dr["DisplayOrder"].DbInt();
                        node.ParentNode = new Shared.Entities.Sitemap.WebTopNode()
                        {
                            ID = dr["ParentNodeID"].DbInt()
                        };
                        node.ChildNodes = new Shared.Entities.Sitemap.WebTopNodeCollection();
                        topnodes.Add(node);
                    }

                    // Bottom Nav
                    dr.NextResult();
                    while (dr.Read())
                    {
                        var node = new Shared.Entities.Sitemap.WebBottomNode();
                        node.ID = dr["NodeID"].DbInt();
                        node.Url = dr["Url"].DbString();
                        node.Title = dr["Title"].DbString();
                        node.DisplayOrder = dr["DisplayOrder"].DbInt();
                        node.ParentNode = new Shared.Entities.Sitemap.WebBottomNode()
                        {
                            ID = dr["ParentNodeID"].DbInt()
                        };
                        node.ChildNodes = new Shared.Entities.Sitemap.WebBottomNodeCollection();
                        bottomnodes.Add(node);
                    }

                    // Left Nav
                    dr.NextResult();
                    data.CurrentLeftNode = new Shared.Entities.Sitemap.WebLeftNode()
                    {
                        ChildNodes = new Shared.Entities.Sitemap.WebLeftNodeCollection(),
                        ParentNode = new Shared.Entities.Sitemap.WebLeftNode()
                        {
                            ChildNodes = new Shared.Entities.Sitemap.WebLeftNodeCollection(),
                            ParentNode = new Shared.Entities.Sitemap.WebLeftNode()
                            {
                                ChildNodes = new Shared.Entities.Sitemap.WebLeftNodeCollection()
                            }
                        }
                    };

                    if (dr.HasRows)
                    {
                        dr.Read();
                        data.CurrentLeftNode.ID = dr["NodeID"].DbInt();
                        data.CurrentLeftNode.Url = dr["Url"].DbString();
                        data.CurrentLeftNode.Title = dr["Title"].DbString();
                        data.CurrentLeftNode.DisplayOrder = dr["DisplayOrder"].DbInt();
                    }

                    dr.NextResult();
                    if (dr.HasRows)
                    {
                        dr.Read();
                        data.CurrentLeftNode.ParentNode.ID = dr["NodeID"].DbInt();
                        data.CurrentLeftNode.ParentNode.Url = dr["Url"].DbString();
                        data.CurrentLeftNode.ParentNode.Title = dr["Title"].DbString();
                        data.CurrentLeftNode.ParentNode.DisplayOrder = dr["DisplayOrder"].DbInt();
                    }

                    dr.NextResult();
                    while (dr.Read())
                    {
                        var node = new Shared.Entities.Sitemap.WebLeftNode();
                        node.ID = dr["NodeID"].DbInt();
                        node.Url = dr["Url"].DbString();
                        node.Title = dr["Title"].DbString();
                        node.DisplayOrder = dr["DisplayOrder"].DbInt();
                        data.CurrentLeftNode.ParentNode.ChildNodes.Add(node);
                    }

                    dr.NextResult();
                    while (dr.Read())
                    {
                        var node = new Shared.Entities.Sitemap.WebLeftNode();
                        node.ID = dr["NodeID"].DbInt();
                        node.Url = dr["Url"].DbString();
                        node.Title = dr["Title"].DbString();
                        node.DisplayOrder = dr["DisplayOrder"].DbInt();
                        data.CurrentLeftNode.ChildNodes.Add(node);
                    }

                    dr.NextResult();
                    while (dr.Read())
                    {
                        var node = new Shared.Entities.Sitemap.WebLeftNode();
                        node.ID = dr["NodeID"].DbInt();
                        node.Url = dr["Url"].DbString();
                        node.Title = dr["Title"].DbString();
                        node.DisplayOrder = dr["DisplayOrder"].DbInt();
                        data.CurrentLeftNode.ParentNode.ParentNode.ChildNodes.Add(node);
                    }
                }

                // Bread Crumb
                dr.NextResult();
                while (dr.Read())
                {
                    var node = new Shared.Entities.Sitemap.Node();
                    node.ID = dr["NodeID"].DbInt();
                    node.Url = dr["Url"].DbString();
                    node.Title = dr["Title"].DbString();
                    data.BreadCrumb.Add(node);
                }
            }

            foreach (var node in topnodes)
            {
                if (node.ID == 1)
                {
                    data.TopHomeNode = node;
                }
                else
                {
                    topnodes.Where(n => n.ID == node.ParentNode.ID).FirstOrDefault().ChildNodes.Add(node);
                }
            }

            foreach (var node in bottomnodes)
            {
                if (node.ID == 1)
                {
                    data.BottomHomeNode = node;
                }
                else
                {
                    bottomnodes.Where(n => n.ID == node.ParentNode.ID).FirstOrDefault().ChildNodes.Add(node);
                }
            }

            return data;
        }
    }
}